using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Framework.Library.Components.Security;
using Framework.Library.Components.Commands;
using Framework.Library;
using Framework.Library.Utilities.Database;
using Framework.Library.Utilities;
using Framework.Library.Components.Database;

namespace Framework.Portal.Administrator.Database
{
    public partial class TablesNew : AuthenticatedControl
    {
        private DataTable dataColumns = new DataTable();

        void Page_Init(object sender, EventArgs e)
        {
            loadDataTable();
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if ((!CurrentUser.IsSuperAdmin && CommandController.IsSystemModule(AppEnv.CommandID())) || !CommandController.IsAvailableForUser(AppEnv.CommandID(), CurrentUser.UserID, Convert.ToInt32(AppEnv.PortalId())))
                Response.Redirect(AppEnv.ADMIN_CMD, true);
            if (!Page.IsPostBack)
            {
                //Load database owner
                dropOwner.DataSource = Info.GetOwners();
                dropOwner.DataValueField = "name";
                dropOwner.DataTextField = "name";
                dropOwner.DataBind();
                Info.CheckDropList(dropOwner, "dbo");

                //Load file groups
                dropFileGroup.Items.Add("Default");
                foreach (DataRow objRow in Info.GetFileGroups().Rows)
                {
                    ListItem myItem = new ListItem();
                    myItem.Value = objRow["groupname"].ToString();
                    myItem.Text = myItem.Value;
                    dropFileGroup.Items.Add(myItem);
                }

                //Load datatype
                foreach (DataRow objRow in Info.GetDataTypes().Rows)
                {
                    ListItem myItem = new ListItem();
                    myItem.Value = objRow["Name"].ToString();
                    myItem.Text = myItem.Value;
                    dropDataType.Items.Add(myItem);
                }
                dropDataType.SelectedIndex = 1;

                //load Defaults
                dropDefaults.Items.Add("");
                foreach (DataRow objRow in Info.GetDefaults().Rows)
                {
                    ListItem myItem = new ListItem();
                    myItem.Value = objRow["Name"].ToString();
                    myItem.Text = myItem.Value;
                    dropDefaults.Items.Add(myItem);
                }

                //Load Rules
                dropRules.Items.Add("");
                foreach (DataRow objRow in Info.GetRules().Rows)
                {
                    ListItem myItem = new ListItem();
                    myItem.Value = objRow["Name"].ToString();
                    myItem.Text = myItem.Value;
                    dropRules.Items.Add(myItem);
                }
                //load javascript
                ltlJavascriptDataType.Text = "";
                ltlJavascriptDataType.Text += "<script language=\"javascript\">\n";
                ltlJavascriptDataType.Text += "function checkDataType(strType){\n";
                ltlJavascriptDataType.Text += "		objWidth = document.getElementById('" + txtWidth.ClientID + "');\n";
                ltlJavascriptDataType.Text += "		objScale = document.getElementById('" + txtScale.ClientID + "');\n";
                ltlJavascriptDataType.Text += "		objScale.disabled = false;\n";
                ltlJavascriptDataType.Text += "		objWidth.disabled = false;\n";
                ltlJavascriptDataType.Text += "		switch(strType){\n";
                //loop on data type
                ltlJavascriptDataType.Text += "			case '':\n";
                ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                ltlJavascriptDataType.Text += "				break;\n";
                foreach (DataRow objRow in Info.GetDataTypes().Rows)
                {
                    ltlJavascriptDataType.Text += "			case '" + objRow["name"].ToString() + "':\n";
                    //special controls
                    switch (objRow["name"].ToString())
                    {
                        case "bigint":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "binary":
                            ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = false;\n";
                            break;
                        case "bit":
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            break;
                        case "char":
                            ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = false;\n";
                            break;
                        case "datetime":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "decimal":
                            ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '0';\n";
                            break;
                        case "float":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "image":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "int":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "money":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "nchar":
                            ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            break;
                        case "ntext":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "numeric":
                            ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '0';\n";
                            break;
                        case "nvarchar":
                            ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            break;
                        case "real":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "smalldatetime":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "smallint":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "smallmoney":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "sql_variant":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "sysname":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "text":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "timestamp":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "tinyint":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "uniqueidentifier":
                            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                            break;
                        case "varbinary":
                            ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            break;
                        case "varchar":
                            ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                            break;
                        default:
                            ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["length"].ToString() + "';\n";
                            ltlJavascriptDataType.Text += "				objScale.value = '" + objRow["scale"].ToString() + "';\n";
                            break;
                    }
                    ltlJavascriptDataType.Text += "				break;\n";
                }
                ltlJavascriptDataType.Text += "		}\n";
                ltlJavascriptDataType.Text += "}\n";
                ltlJavascriptDataType.Text += "</script>";

                //associate javascript to drop
                dropDataType.Attributes.Add("onChange", "checkDataType(this.options[this.selectedIndex].value)");
                gridColumns.DataSource = dataColumns;
                gridColumns.DataBind();
            }
        }

        private void loadDataTable()
        {
            dataColumns.Columns.Add("name", Type.GetType("System.String"));
            dataColumns.Columns.Add("datatype", Type.GetType("System.String"));
            dataColumns.Columns.Add("width", Type.GetType("System.String"));
            dataColumns.Columns.Add("scale", Type.GetType("System.String"));
            dataColumns.Columns.Add("identity", Type.GetType("System.String"));
            dataColumns.Columns.Add("seed", Type.GetType("System.String"));
            dataColumns.Columns.Add("increment", Type.GetType("System.String"));
            dataColumns.Columns.Add("default", Type.GetType("System.String"));
            dataColumns.Columns.Add("defaultbinding", Type.GetType("System.String"));
            dataColumns.Columns.Add("isnull", Type.GetType("System.String"));
            dataColumns.Columns.Add("ruleBinding", Type.GetType("System.String"));
        }

        private void resetForm()
        {
            txtColumnName.Text = "";
            dropDataType.SelectedIndex = 0;
            txtWidth.Text = "";
            txtScale.Text = "";
            radioNull.SelectedIndex = 0;
            checkIdentity.Checked = false;
            txtSeed.Enabled = false;
            txtIncrement.Enabled = false;
            txtDefault.Text = "";
            dropDefaults.SelectedIndex = 0;
            btnUpdate.Visible = false;
            ltlExecuteJavascript.Text = "";
            dropRules.SelectedIndex = 0;
        }
        protected void btnAdd_Click(object sender, EventArgs e)
        {
            if (Page.IsValid)
            {
                foreach (GridViewRow objRow in gridColumns.Rows)
                {
                    DataRow myRow = dataColumns.NewRow();
                    myRow["name"] = objRow.Cells[0].Text;
                    myRow["datatype"] = objRow.Cells[1].Text;
                    myRow["width"] = objRow.Cells[2].Text;
                    myRow["scale"] = objRow.Cells[3].Text;

                    myRow["identity"] = objRow.Cells[4].Text;
                    myRow["seed"] = objRow.Cells[5].Text;
                    myRow["increment"] = objRow.Cells[6].Text;

                    myRow["default"] = objRow.Cells[7].Text;
                    myRow["defaultbinding"] = objRow.Cells[8].Text;
                    myRow["ruleBinding"] = objRow.Cells[9].Text;
                    myRow["isnull"] = objRow.Cells[10].Text;
                    dataColumns.Rows.Add(myRow);

                }

                DataRow myRowNew = dataColumns.NewRow();
                myRowNew["name"] = txtColumnName.Text;
                myRowNew["datatype"] = dropDataType.SelectedValue;

                if (txtWidth.Text != "" || txtWidth.Text != "0") myRowNew["width"] = txtWidth.Text;
                if (txtScale.Text != "" || txtScale.Text != "0") myRowNew["scale"] = txtScale.Text;

                myRowNew["isnull"] = radioNull.SelectedValue;

                myRowNew["identity"] = checkIdentity.Checked.ToString();

                if (checkIdentity.Checked)
                {
                    myRowNew["seed"] = txtSeed.Text;
                    myRowNew["increment"] = txtIncrement.Text;
                }
                else
                {
                    myRowNew["seed"] = "";
                    myRowNew["increment"] = "";
                }

                myRowNew["default"] = txtDefault.Text;
                myRowNew["defaultbinding"] = dropDefaults.SelectedValue;
                myRowNew["ruleBinding"] = dropRules.SelectedValue;

                dataColumns.Rows.Add(myRowNew);

                gridColumns.DataSource = dataColumns;
                gridColumns.DataBind();

                //reset the form
                resetForm();
            }
        }
        protected void gridColumns_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            //reload gridview item
            foreach (GridViewRow objRow in gridColumns.Rows)
            {
                DataRow myRow = dataColumns.NewRow();
                myRow["name"] = objRow.Cells[0].Text;
                myRow["datatype"] = objRow.Cells[1].Text;
                myRow["width"] = objRow.Cells[2].Text;
                myRow["scale"] = objRow.Cells[3].Text;

                myRow["identity"] = objRow.Cells[4].Text;
                myRow["seed"] = objRow.Cells[5].Text;
                myRow["increment"] = objRow.Cells[6].Text;

                myRow["default"] = objRow.Cells[7].Text;
                myRow["defaultbinding"] = objRow.Cells[8].Text;
                myRow["ruleBinding"] = objRow.Cells[9].Text;
                myRow["isnull"] = objRow.Cells[10].Text;
                dataColumns.Rows.Add(myRow);
            }

            dataColumns.Rows[e.RowIndex].Delete();

            gridColumns.DataSource = dataColumns;
            gridColumns.DataBind();
        }
        protected void gridColumns_RowEditing(object sender, GridViewEditEventArgs e)
        {
            resetForm();

            btnUpdate.Visible = true;
            txtColumnName.Text = gridColumns.Rows[e.NewEditIndex].Cells[0].Text.ToString();
            Info.CheckDropList(dropDataType, gridColumns.Rows[e.NewEditIndex].Cells[1].Text.ToString());

            if (gridColumns.Rows[e.NewEditIndex].Cells[2].Text.ToString() == "&nbsp;")
            {
                ltlExecuteJavascript.Text += "<script>\n checkDataType('" + gridColumns.Rows[e.NewEditIndex].Cells[1].Text.ToString() + "');\n</script>";
            }
            else
            {
                txtWidth.Text = gridColumns.Rows[e.NewEditIndex].Cells[2].Text.ToString();
                txtScale.Text = gridColumns.Rows[e.NewEditIndex].Cells[3].Text.ToString().Replace("&nbsp;", "0");
            }

            checkIdentity.Checked = Convert.ToBoolean(gridColumns.Rows[e.NewEditIndex].Cells[4].Text.ToString());

            if (checkIdentity.Checked)
            {
                txtSeed.Enabled = true;
                txtIncrement.Enabled = true;
                txtSeed.Text = gridColumns.Rows[e.NewEditIndex].Cells[5].Text.ToString();
                txtIncrement.Text = gridColumns.Rows[e.NewEditIndex].Cells[6].Text.ToString();
            }
            else
            {
                txtSeed.Text = "1";
                txtIncrement.Text = "1";
            }

            Info.CheckRadioList(radioNull, gridColumns.Rows[e.NewEditIndex].Cells[9].Text.ToString());

            txtDefault.Text = gridColumns.Rows[e.NewEditIndex].Cells[7].Text.ToString().Replace("&nbsp;", "");
            Info.CheckDropList(dropDefaults, gridColumns.Rows[e.NewEditIndex].Cells[8].Text.ToString());

            hideRowUpdate.Value = e.NewEditIndex.ToString();
        }
        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            //reload rows
            foreach (GridViewRow objRow in gridColumns.Rows)
            {
                DataRow myRow = dataColumns.NewRow();
                myRow["name"] = objRow.Cells[0].Text;
                myRow["datatype"] = objRow.Cells[1].Text;
                myRow["width"] = objRow.Cells[2].Text;
                myRow["scale"] = objRow.Cells[3].Text;

                myRow["identity"] = objRow.Cells[4].Text;
                myRow["seed"] = objRow.Cells[5].Text;
                myRow["increment"] = objRow.Cells[6].Text;

                myRow["default"] = objRow.Cells[7].Text;
                myRow["defaultbinding"] = objRow.Cells[8].Text;
                myRow["ruleBinding"] = objRow.Cells[9].Text;
                myRow["isnull"] = objRow.Cells[10].Text;
                dataColumns.Rows.Add(myRow);
            }

            //update the selected row
            DataRow myRowNew = dataColumns.Rows[Convert.ToInt32(hideRowUpdate.Value)];
            myRowNew["name"] = txtColumnName.Text;
            myRowNew["datatype"] = dropDataType.SelectedValue;
            myRowNew["width"] = txtWidth.Text;
            myRowNew["scale"] = txtScale.Text;

            myRowNew["isnull"] = radioNull.SelectedValue;

            myRowNew["identity"] = checkIdentity.Checked.ToString();

            if (checkIdentity.Checked)
            {
                myRowNew["seed"] = txtSeed.Text;
                myRowNew["increment"] = txtIncrement.Text;
            }
            else
            {
                myRowNew["seed"] = "";
                myRowNew["increment"] = "";
            }

            myRowNew["default"] = txtDefault.Text;
            myRowNew["defaultbinding"] = dropDefaults.SelectedValue;
            myRowNew["ruleBinding"] = dropRules.SelectedValue;

            gridColumns.DataSource = dataColumns;
            gridColumns.DataBind();

            resetForm();
        }
        protected void btnCreate_Click(object sender, EventArgs e)
        {            
            //create table
            _Table objTable = new _Table();
            try
            {
                //loop on gridrow items and create columns collection
                foreach (GridViewRow objRow in gridColumns.Rows)
                {
                    _Column myColumn = new _Column();
                    myColumn.Name = objRow.Cells[0].Text;
                    myColumn.Type = objRow.Cells[1].Text;
                    myColumn.Width = objRow.Cells[2].Text.Replace("&nbsp;", "");
                    myColumn.Scale = objRow.Cells[3].Text.Replace("&nbsp;", "");
                    myColumn.IsIdentity = Convert.ToBoolean(objRow.Cells[4].Text);
                    myColumn.Seed = objRow.Cells[5].Text.Replace("&nbsp;", "");
                    myColumn.Increment = objRow.Cells[6].Text.Replace("&nbsp;", "");
                    myColumn.Default = objRow.Cells[7].Text.Replace("&nbsp;", "");
                    myColumn.DefaultBinding = objRow.Cells[8].Text.Replace("&nbsp;", "");
                    myColumn.RuleBinding = objRow.Cells[9].Text.Replace("&nbsp;", "");
                    myColumn.AllowNulls = Convert.ToBoolean(objRow.Cells[10].Text);

                    objTable.Columns.Add(myColumn);
                }                
                objTable.Name = txtTableName.Text;
                objTable.Owner = dropOwner.SelectedValue;

                objTable.FileGroup = dropFileGroup.SelectedValue;
                objTable.Create();
                Response.Redirect(AppEnv.AdminUrlParams(CommandController.GetCommandbyID((int)PortalControls.Table)));
            }
            catch (Exception ex)
            {
                lblError.Visible = true;
                lblError.Text = "<p>" + ex.Message + "</p>";
            }
        }
        protected void checkIdentity_CheckedChanged(object sender, EventArgs e)
        {
            txtSeed.Enabled = checkIdentity.Checked;
            txtIncrement.Enabled = checkIdentity.Checked;
        }
    }
}